﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class TelBookDAL
    {
        public static readonly SqlConnectionStringBuilder DispatchBuilder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);//取调度库的链接字符串
        public static readonly string DispatchDatabase = DispatchBuilder.InitialCatalog;

        //查询电话本
        public object GetTelBookList(int pageSize, int pageIndex, string phoneType, string name, string phone,
                                     E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID, string eventDispatchCenterID)
        {
            List<TelBookInfo> list = new List<TelBookInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select identity(int,1,1) as 行号,名称=ttb.名称,电话分类=tztt.名称,联系电话一=ttb.联系电话一,
                               分机一=ttb.分机一 ,联系电话二=ttb.联系电话二,分机二=ttb.分机二,备注=ttb.备注
                        into #tempa
                        from TTelBook ttb 
                        left join TZTelType tztt on tztt.编码=ttb.电话分类编码 
                        where 1=1 ");
            WhereClauseUtility.AddStringEqual("ttb.电话分类编码", phoneType, sb);
            WhereClauseUtility.AddStringLike("ttb.名称", name, sb);
            if (eventDispatchCenterID == "1")
                sb.Append(" and ttb.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ttb.中心编码", eventDispatchCenterID, sb); //2020-11-23 hsp  
            sb.Append(" and (联系电话一 like '%" + phone + "%' or 分机一 like '%" + phone + "%' ");
            sb.Append("  or 联系电话二 like '%" + phone + "%' or 分机二 like '%" + phone + "%' or 备注 like '%" + phone + "%' ) "); 
               
            sb.Append(@" 
                    select * into #temp from #tempa where 1=1 ");

            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #tempa,#temp ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    TelBookInfo info = new TelBookInfo();
                    info.name = dr["名称"].ToString();
                    info.phoneType = dr["电话分类"].ToString();
                    info.phone1 = dr["联系电话一"].ToString();
                    info.extension1 = dr["分机一"].ToString();
                    info.phone2 = dr["联系电话二"].ToString();
                    info.extension2 = dr["分机二"].ToString();
                    info.remark = dr["备注"].ToString();                  
                    list.Add(info);
                }
            }

            int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            var result = new { total = total, rows = list };
            return result;
        }

        //获取电话类型
        public object GetPhoneTypeList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称 from TZTelType where 是否有效 = 1");
            if (eventDispatchCenterID == "1")
                sb.Append(" and 中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

    }
}
